﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SMSModels;

namespace DAL.GoodsManager
{
    public class InStoreService
    {
        /// <summary>
        /// 获取入库表数据集对象
        /// </summary>
        /// <returns>返回匹配的数据集对象</returns>
        public DataSet GetDataSetFromInStoreTable()
        {
            string sql = "select 入库编号,入库表.货物编号,货物名称,入库表.仓库ID,仓库名称 as 所入仓库,入库表.供货商ID,供货商名称,规格,计量单位,入库单价,入库数量,入库总金额,入库时间,经手人,入库表.备注 from (select 货物编号,货物名称,供货商信息表.供货商名称,规格,计量单位,单价,货物档案表.备注 from 货物档案表,供货商信息表 where 货物档案表.供货商ID=供货商信息表.供货商ID) a,入库表,仓库信息表 where 入库表.货物编号=a.货物编号 and 入库表.仓库ID=仓库信息表.仓库ID";
            string tbName = "inStoreInfo";
            return SQLHelper.GetDataSet(sql, tbName);
        }


        /// <summary>
        /// 货物入库
        /// </summary>
        /// <param name="inStore"></param>
        /// <returns></returns>
        public int InStore(InStore inStore)
        {
            //[1]封装SQL语句，入库
            string sql = string.Format("insert into 入库表 (入库编号,货物编号,仓库ID,供货商ID,入库单价,入库数量,入库总金额,入库时间,经手人,备注) values('{0}','{1}',{2},{3},{4},{5},{6},'{7}','{8}','{9}')", inStore.InStoreID, inStore.GoodsID, inStore.StoreID, inStore.SupplierID, inStore.InStoreMonovalent, inStore.InStoreQuantity, inStore.InStoreTotal, inStore.InStoreTime, inStore.Handler, inStore.Remarks);

            return SQLHelper.Update(sql);
        }


        /// <summary>
        /// 记录是否存在于库存表中
        /// </summary>
        /// <param name="inStore"></param>
        /// <returns>存在返回true，不存在返回false</returns>
        public bool IsExists(InStore inStore)
        {
            //[1]封装SQL语句
            string sqlIsExists = string.Format("select * from 库存表 where 货物编号='{0}' and 仓库ID={1}", inStore.GoodsID, inStore.StoreID);
            System.Data.SQLite.SQLiteDataReader reader = SQLHelper.GetReader(sqlIsExists);
            if (reader.HasRows)    //存在
            {
                reader.Close();
                return true;
            }
            else    //不存在
            {
                reader.Close();
                return false;
            }
        }


        /// <summary>
        /// 入库时更新库存数量
        /// </summary>
        /// <param name="inStore"></param>
        /// <returns></returns>
        public int UpdateStorageOnInStore(InStore inStore)
        {
            //[1]封装SQL语句
            string sqlUpdateStorage = string.Format("UPDATE 库存表 SET 库存数量=库存表.库存数量+{0} WHERE 库存表.货物编号='{1}' and 仓库ID={2}", inStore.InStoreQuantity, inStore.GoodsID, inStore.StoreID);
            //提交  更新库存
            return SQLHelper.Update(sqlUpdateStorage);

        }


        /// <summary>
        /// 插入 库存表 数据(当库存中不存在入库记录时)
        /// </summary>
        /// <param name="inStore">库存对象</param>
        /// <returns></returns>
        public int InsertStorageTable(InStore inStore)
        {
            //[1]封装SQL语句
            string sqlInsertStorageTable = string.Format("insert into 库存表 (货物编号,仓库ID,供货商ID,入库单价,库存数量,备注)values('{0}',{1},{2},{3},{4},'{5}')", inStore.GoodsID, inStore.StoreID, inStore.SupplierID, inStore.InStoreMonovalent, inStore.InStoreQuantity, inStore.Remarks);
            //提交 插入库存表数据
            return SQLHelper.Update(sqlInsertStorageTable);

        }


        /// <summary>
        /// 修改入库信息
        /// </summary>
        /// <param name="inStore"></param>
        /// <returns></returns>
        public int ReviseInStoreInfo(InStore inStore)
        {
            //[1]封装SQL语句，修改入库信息
            string sql = string.Format("update 入库表 set 货物编号='{0}',仓库ID={1},供货商ID={2},入库单价={3},入库数量={4},入库总金额={5},入库时间='{6}',经手人='{7}',备注='{8}' where 入库编号='{9}'", inStore.GoodsID, inStore.StoreID, inStore.SupplierID, inStore.InStoreMonovalent, inStore.InStoreQuantity, inStore.InStoreTotal, inStore.InStoreTime, inStore.Handler, inStore.Remarks, inStore.InStoreID);

            //[2]提交修改
            return SQLHelper.Update(sql);
        }


        /// <summary>
        /// 删除入库信息
        /// </summary>
        /// <param name="inStore"></param>
        /// <returns></returns>
        public int DeleteInStoreInfo(InStore inStore)
        {
            //[1]封装SQL语句，删除入库信息
            string sql = string.Format("delete from 入库表 where 入库编号='{0}'", inStore.InStoreID);

            //[2]提交删除
            return SQLHelper.Update(sql);

        }
    }
}
